﻿namespace QLPhongKham.Access.Duoc
{
    using ConfigDatabase.Class;
    using Npgsql;
    using QLPhongKham.Access;
    using QLPhongKham.BaseClass.Duoc;
    using System;
    using System.Data;

    public class DTonKho
    {
        private ConnectDatabase _condata;
        private NpgsqlConnection _conn;
        private Access.Duoc.DThongSo _tsduoc;
        private General _xuli;

        public DTonKho()
        {
            this._condata = new ConnectDatabase();
            this._xuli = new General(this._condata);
            this._tsduoc = new Access.Duoc.DThongSo(this._condata);
        }

        public DTonKho(ConnectDatabase condata)
        {
            this._condata = condata;
            this._xuli = new General(this._condata);
            this._tsduoc = new Access.Duoc.DThongSo(this._condata);
        }

        private string f_getsql_nhapchitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            return ("select b1." + dtoDDMDuocPham.columnID + " as id,b1." + dtoDDMDuocPham.columnMA + " as ma,b1." + dtoDDMDuocPham.columnTEN + " as ten,b1." + dtoDDMDuocPham.columnTENHOATCHAT + " as tenhoatchat,b1." + dtoDDMDuocPham.columnDVT + " as dvt,b1." + dtoDDMDuocPham.columnHAMLUONG + " as hamluong,b." + dtoDNhapKhoCT.columnHANGSX + " as hangsx,to_char(b." + dtoDNhapKhoCT.columnHANDUNG + ",'dd/mm/yyyy') as handung,b." + dtoDNhapKhoCT.columnLO + " as losx,b." + dtoDNhapKhoCT.columnGIABAN + " as gianhap,sum(b." + dtoDNhapKhoCT.columnSOLUONG + ") as slnhap,a." + dtoDNhapKho.columnHOADON + " as hoadon,b1." + dtoDDMDuocPham.columnIDNHOM + " as idnhom,b1." + dtoDDMDuocPham.columnIDLOAI + " as idloai,b11." + dtoDDMNhomDuocPham.columnTEN + " as tennhom,b12." + dtoDDMLoaiDuocPham.columnTEN + " as tenloai from " + str + dtoDNhapKho.tableName + " a inner join " + str + dtoDNhapKhoCT.tableName + " b on a." + dtoDNhapKho.columnID + "=b." + dtoDNhapKhoCT.columnIDNHAPKHO + " inner join " + str + dtoDDMDuocPham.tableName + " b1 on b." + dtoDNhapKhoCT.columnIDDUOCPHAM + "=b1." + dtoDDMDuocPham.columnID + " left join " + str + dtoDDMNhomDuocPham.tableName + " b11 on b1." + dtoDDMDuocPham.columnIDNHOM + "=b11." + dtoDDMNhomDuocPham.columnID + " left join " + str + dtoDDMLoaiDuocPham.tableName + " b12 on b1." + dtoDDMDuocPham.columnIDLOAI + "=b12." + dtoDDMLoaiDuocPham.columnID + " where 1=1 " + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDNhapKho.columnNGAY + ",'" + formatdate + "'),'" + formatdate + "')>=to_timestamp('" + tungay + "','" + formatdate + "')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDNhapKho.columnNGAY + ",'" + formatdate + "'),'" + formatdate + "')<=to_timestamp('" + denngay + "','" + formatdate + "')")) + ((manhom != "") ? (" and b1." + dtoDDMDuocPham.columnIDNHOM + " in (" + manhom + ")") : "") + ((maloai != "") ? (" and b1." + dtoDDMDuocPham.columnIDLOAI + " in (" + maloai + ")") : "") + "  group by b1." + dtoDDMDuocPham.columnID + ",b1." + dtoDDMDuocPham.columnMA + ",b1." + dtoDDMDuocPham.columnTEN + ",b1." + dtoDDMDuocPham.columnTENHOATCHAT + ",b1." + dtoDDMDuocPham.columnDVT + ",b1." + dtoDDMDuocPham.columnHAMLUONG + ",b." + dtoDNhapKhoCT.columnHANDUNG + ",b." + dtoDNhapKhoCT.columnLO + ",b." + dtoDNhapKhoCT.columnHANGSX + ",b." + dtoDNhapKhoCT.columnGIABAN + ",a." + dtoDNhapKho.columnHOADON + ",b1." + dtoDDMDuocPham.columnIDLOAI + ",b1." + dtoDDMDuocPham.columnIDNHOM + ",b11." + dtoDDMNhomDuocPham.columnTEN + ",b12." + dtoDDMLoaiDuocPham.columnTEN + " order by b1." + dtoDDMDuocPham.columnTEN);
        }

        private string f_getsql_tonkhoct(int idduocpham, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            return ("select a." + dtoDNhapKho.columnNGAY + " as ngaynhapkho,b." + dtoDNhapKhoCT.columnID + " as idnhapct,b." + dtoDNhapKhoCT.columnGIABAN + " as giaban,b." + dtoDNhapKhoCT.columnGIAMUA + " as giamua,b." + dtoDNhapKhoCT.columnSOLUONG + "-b." + dtoDNhapKhoCT.columnSOLUONGXUAT + " as soluongton,to_char(b." + dtoDNhapKhoCT.columnHANDUNG + ",'dd/mm/yyyy') as handung,b." + dtoDNhapKhoCT.columnHANDUNG + " as dat,b." + dtoDNhapKhoCT.columnLO + " as losx,b." + dtoDNhapKhoCT.columnHANGSX + " as hangsx,b2." + dtoDDMDuocPham.columnID + " as iddp,b2." + dtoDDMDuocPham.columnMA + " as madp,b2." + dtoDDMDuocPham.columnTEN + " as tendp,b2." + dtoDDMDuocPham.columnTENHOATCHAT + " as tenhoatchat,b2." + dtoDDMDuocPham.columnDVT + " as dvt,b2." + dtoDDMDuocPham.columnHAMLUONG + " as hamluong,b." + dtoDNhapKhoCT.columnIDVITRIKHO + " as idvitrikho,b3." + dtoDDMViTriTrongKho.columnTEN + " as tenvitrikho,b2." + dtoDDMDuocPham.columnIDLOAI + " as idloai,b2." + dtoDDMDuocPham.columnIDNHOM + " as idnhom,b22." + dtoDDMLoaiDuocPham.columnTEN + " as tenloai,b21." + dtoDDMNhomDuocPham.columnTEN + " as tennhom from " + str + dtoDNhapKho.tableName + " a inner join " + str + dtoDNhapKhoCT.tableName + " b on a." + dtoDNhapKho.columnID + "=b." + dtoDNhapKhoCT.columnIDNHAPKHO + " inner join " + str + dtoDDMDuocPham.tableName + " b2 on b2." + dtoDDMDuocPham.columnID + "=b." + dtoDNhapKhoCT.columnIDDUOCPHAM + " left join " + str + dtoDDMViTriTrongKho.tableName + " b3 on b3." + dtoDDMViTriTrongKho.columnID + "=b." + dtoDNhapKhoCT.columnIDVITRIKHO + " left join " + str + dtoDDMNhomDuocPham.tableName + " b21 on b21." + dtoDDMNhomDuocPham.columnID + "=b2." + dtoDDMDuocPham.columnIDNHOM + " left join " + str + dtoDDMLoaiDuocPham.tableName + " b22 on b22." + dtoDDMLoaiDuocPham.columnID + "=b2." + dtoDDMDuocPham.columnIDLOAI + " where b." + dtoDNhapKhoCT.columnSOLUONGXUAT + "<b." + dtoDNhapKhoCT.columnSOLUONG + ((idduocpham == 0) ? "" : string.Concat(new object[] { " and b2.", dtoDDMDuocPham.columnID, "=", idduocpham })) + ((manhom != "") ? (" and b2." + dtoDDMDuocPham.columnIDNHOM + " in (" + manhom + ")") : "") + ((maloai != "") ? (" and b2." + dtoDDMDuocPham.columnIDLOAI + " in (" + maloai + ")") : "") + " order by b2." + dtoDDMDuocPham.columnTEN);
        }

        private string f_getsql_xuatchitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            return ("select b1." + dtoDDMDuocPham.columnID + " as id,b1." + dtoDDMDuocPham.columnMA + " as ma,b1." + dtoDDMDuocPham.columnTEN + " as ten,b1." + dtoDDMDuocPham.columnTENHOATCHAT + " as tenhoatchat,b1." + dtoDDMDuocPham.columnDVT + " as dvt,b1." + dtoDDMDuocPham.columnHAMLUONG + " as hamluong,b21." + dtoDNhapKhoCT.columnID + " as idnhapkhoct,b21." + dtoDNhapKhoCT.columnLO + " as losx,to_char(b21." + dtoDNhapKhoCT.columnHANDUNG + ",'dd/mm/yyyy') as handung,b21." + dtoDNhapKhoCT.columnHANGSX + " as hangsx,b21." + dtoDNhapKhoCT.columnGIABAN + " as gianhap,sum(b2." + dtoDTheoDoiXuatKho.columnSOLUONG + ") as slxuat,b1." + dtoDDMDuocPham.columnIDNHOM + " as idnhom,b1." + dtoDDMDuocPham.columnIDLOAI + " as idloai,b11." + dtoDDMNhomDuocPham.columnTEN + " as tennhom,b12." + dtoDDMLoaiDuocPham.columnTEN + " as tenloai from " + str + dtoDXuatBanLe.tableName + " a inner join " + str + dtoDXuatBanLeCT.tableName + " b on a." + dtoDXuatBanLe.columnID + "=b." + dtoDXuatBanLeCT.columnIDXUATKHOBANLE + " inner join " + str + dtoDTheoDoiXuatKho.tableName + " b2 on b." + dtoDXuatBanLeCT.columnID + "=b2." + dtoDTheoDoiXuatKho.columnIDXUATKHOCT + " inner join " + str + dtoDNhapKhoCT.tableName + " b21 on b21." + dtoDNhapKhoCT.columnID + "=b2." + dtoDTheoDoiXuatKho.columnIDNHAPKHOCT + " inner join " + str + dtoDDMDuocPham.tableName + " b1 on b." + dtoDXuatBanLeCT.columnIDDUOCPHAM + "=b1." + dtoDDMDuocPham.columnID + " left join " + str + dtoDDMNhomDuocPham.tableName + " b11 on b1." + dtoDDMDuocPham.columnIDNHOM + "=b11." + dtoDDMNhomDuocPham.columnID + " left join " + str + dtoDDMLoaiDuocPham.tableName + " b12 on b1." + dtoDDMDuocPham.columnIDLOAI + "=b12." + dtoDDMLoaiDuocPham.columnID + " where 1=1 " + ((tungay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDXuatBanLe.columnNGAY + ",'" + formatdate + "'),'" + formatdate + "')>=to_timestamp('" + tungay + "','" + formatdate + "')")) + ((denngay == "") ? "" : (" and to_timestamp(to_char(a." + dtoDXuatBanLe.columnNGAY + ",'" + formatdate + "'),'" + formatdate + "')<=to_timestamp('" + denngay + "','" + formatdate + "')")) + ((manhom != "") ? (" and b1." + dtoDDMDuocPham.columnIDNHOM + " in (" + manhom + ")") : "") + ((maloai != "") ? (" and b1." + dtoDDMDuocPham.columnIDLOAI + " in (" + maloai + ")") : "") + "  group by b1." + dtoDDMDuocPham.columnID + ",b1." + dtoDDMDuocPham.columnMA + ",b1." + dtoDDMDuocPham.columnTEN + ",b1." + dtoDDMDuocPham.columnTENHOATCHAT + ",b1." + dtoDDMDuocPham.columnDVT + ",b1." + dtoDDMDuocPham.columnHAMLUONG + ",b21." + dtoDNhapKhoCT.columnID + ",b21." + dtoDNhapKhoCT.columnLO + ",b21." + dtoDNhapKhoCT.columnHANDUNG + ",b21." + dtoDNhapKhoCT.columnHANGSX + ",b21." + dtoDNhapKhoCT.columnGIABAN + ",b1." + dtoDDMDuocPham.columnIDLOAI + ",b1." + dtoDDMDuocPham.columnIDNHOM + ",b11." + dtoDDMNhomDuocPham.columnTEN + ",b12." + dtoDDMLoaiDuocPham.columnTEN + " order by b1." + dtoDDMDuocPham.columnTEN);
        }

        public long f_insert_tonkho(dtoDNhapKho nhapkho, dtoDNhapKhoCT[] chitiet)
        {
            long num2;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            try
            {
                nhapkho.pID = long.Parse(this._xuli.f_Get_CapID());
                if (!nhapkho.Insert(this._conn))
                {
                    return 0L;
                }
                NpgsqlTransaction transaction = this._conn.BeginTransaction();
                int index = 0;
                index = 0;
                while (index < chitiet.Length)
                {
                    chitiet[index].pIDNHAPKHO = nhapkho.pID;
                    if (chitiet[index].pID != 0L)
                    {
                        if (!chitiet[index].Update(this._conn))
                        {
                            transaction.Rollback();
                            break;
                        }
                    }
                    else
                    {
                        chitiet[index].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[index].Insert(this._conn))
                        {
                            chitiet[index].pID = 0L;
                            transaction.Rollback();
                            break;
                        }
                    }
                    index++;
                }
                if (index == chitiet.Length)
                {
                    transaction.Commit();
                    return nhapkho.pID;
                }
                nhapkho.Delete();
                num2 = 0L;
            }
            catch
            {
                num2 = 0L;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return num2;
        }

        public DataSet f_load_nhapchitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            string sql = "select  id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,sum(slnhap) as slnhap,idnhom,idloai,tennhom,tenloai from (" + this.f_getsql_nhapchitiet(tungay, denngay, formatdate, manhom, maloai) + ") a where 1=1   group by id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,idnhom,idloai,tennhom,tenloai order by ten";
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_nhaptonghop(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            string sql = "select  id, ma, ten, tenhoatchat, dvt, hamluong,handung,losx,sum(slnhap) as slnhap from (" + this.f_getsql_nhapchitiet(tungay, denngay, formatdate, manhom, maloai) + ") a where 1=1  group by id,ma,ten,tenhoatchat,dvt,hamluong,handung,losx order by ten";
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_sonhapchitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            string sql = "select  id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,sum(slnhap) as slnhap,hoadon from (" + this.f_getsql_nhapchitiet(tungay, denngay, formatdate, manhom, maloai) + ") a where 1=1   group by id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,hoadon order by ten";
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_tonkhochitiet(HangSo.NhomKhachHang.enNhomKhachHang nhomkh, int idduocpham)
        {
            int num = 0;
            string str = "";
            if (nhomkh == HangSo.NhomKhachHang.enNhomKhachHang.DichVu)
            {
                num = this._tsduoc.pApGia_PTGiaDichVu;
            }
            else
            {
                num = this._tsduoc.pApGia_PTGiaThuong;
            }
            if (this._tsduoc.pApGia_TheoGiaNhapMax)
            {
                str = " select max(" + dtoDNhapKhoCT.columnGIABAN + ") as giaban," + dtoDNhapKhoCT.columnIDDUOCPHAM + " as idduocpham from " + this._xuli.pSchema + "." + dtoDNhapKhoCT.tableName + " where " + dtoDNhapKhoCT.columnSOLUONG + ">" + dtoDNhapKhoCT.columnSOLUONGXUAT + " group by " + dtoDNhapKhoCT.columnIDDUOCPHAM;
            }
            string str2 = " order by ";
            if (this._tsduoc.pXuat_NhapTruocXuatTruoc)
            {
                str2 = str2 + " ngaynhapkho asc,idnhapct asc";
            }
            else if (this._tsduoc.pXuat_XuatTheoDate)
            {
                str2 = str2 + " dat desc";
            }
            else
            {
                str2 = str2 + " ngaynhapkho asc,idnhapct asc";
            }
            DataSet set = new DataSet();
            string sql = string.Concat(new object[] { "select a.iddp,madp,tendp,tenhoatchat,dvt,sum(soluongton) as soluongton", (str != "") ? ",b" : ",a", ".giaban*(1+", num, ".0/100) as giaban,ngaynhapkho,idnhapct,tenvitrikho,losx,handung,dat from(", this.f_getsql_tonkhoct(idduocpham, "", ""), ") a ", (str != "") ? (" inner join (" + str + ") b on a.iddp=b.idduocpham") : "", " group by iddp,tendp,dvt,madp", (str != "") ? ",b" : ",a", ".giaban,tenhoatchat ,ngaynhapkho,idnhapct,tenvitrikho,losx,handung,dat", str2 });
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_tonkhochitiet(string manhom, string maloai)
        {
            return this.f_load_tonkhochitiet(0, manhom, maloai);
        }

        public DataSet f_load_tonkhochitiet(int idduocpham, string manhom, string maloai)
        {
            DataSet set = new DataSet();
            string sql = this.f_getsql_tonkhoct(idduocpham, manhom, maloai);
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_tonkhochitiet(string ngay, string formatdate, string manhom, string maloai)
        {
            DataRow row;
            DataRow[] rowArray;
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            DataSet set2 = this.f_load_tonkhochitiet(manhom, maloai);
            DataSet set3 = this.f_load_nhapchitiet(ngay, DateTime.Now.ToString(formatdate), formatdate, manhom, maloai);
            DataSet set4 = this.f_load_xuatchitiet(ngay, DateTime.Now.ToString(formatdate), formatdate, manhom, maloai);
            string sql = "select 0.0 as iddp,'' as madp,'' as tendp,'' as tenhoatchat,'' as dvt,'' as hamluong,'' as losx,'' as handung,'' as hangsx,'' as tenvitrikho,0.0  as gianhap,0.0  as sltondau,0.0 as slnhap,0.0 as slxuat,0.0 as sltoncuoi ,0.0 as idloai,0.0 as idnhom,'' as tenloai,'' as tennhom from " + str + dtoDDMDuocPham.tableName + " where 1=0";
            set = this._xuli.f_Get_dulieu(sql);
            foreach (DataRow row2 in set2.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["iddp"].ToString() + " and losx='" + row2["losx"].ToString() + "' and gianhap=" + row2["giaban"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["iddp"].ToString();
                    row["madp"] = row2["madp"].ToString();
                    row["tendp"] = row2["tendp"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["hamluong"] = row2["hamluong"].ToString();
                    row["losx"] = row2["losx"].ToString();
                    row["handung"] = row2["handung"].ToString();
                    row["hangsx"] = row2["hangsx"].ToString();
                    row["tenvitrikho"] = row2["tenvitrikho"].ToString();
                    row["idnhom"] = row2["idnhom"].ToString();
                    row["idloai"] = row2["idloai"].ToString();
                    row["tennhom"] = row2["tennhom"].ToString();
                    row["tenloai"] = row2["tenloai"].ToString();
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = 0;
                    row["gianhap"] = decimal.Parse(row2["giaban"].ToString());
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["sltoncuoi"] = decimal.Parse(row["sltoncuoi"].ToString()) + decimal.Parse(row2["soluongton"].ToString());
                row["sltondau"] = decimal.Parse(row["sltondau"].ToString()) + decimal.Parse(row2["soluongton"].ToString());
            }
            foreach (DataRow row2 in set3.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["id"].ToString() + " and losx='" + row2["losx"].ToString() + "' and gianhap=" + row2["gianhap"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["id"].ToString();
                    row["madp"] = row2["ma"].ToString();
                    row["tendp"] = row2["ten"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["hamluong"] = row2["hamluong"].ToString();
                    row["losx"] = row2["losx"].ToString();
                    row["handung"] = row2["handung"].ToString();
                    row["hangsx"] = row2["hangsx"].ToString();
                    row["idnhom"] = row2["idnhom"].ToString();
                    row["idloai"] = row2["idloai"].ToString();
                    row["tennhom"] = row2["tennhom"].ToString();
                    row["tenloai"] = row2["tenloai"].ToString();
                    row["tenvitrikho"] = "";
                    row["gianhap"] = decimal.Parse(row2["gianhap"].ToString());
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = 0;
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["slnhap"] = decimal.Parse(row["slnhap"].ToString()) + decimal.Parse(row2["slnhap"].ToString());
                row["sltondau"] = decimal.Parse(row["sltoncuoi"].ToString()) - decimal.Parse(row["slnhap"].ToString());
            }
            foreach (DataRow row2 in set4.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["id"].ToString() + " and losx='" + row2["losx"].ToString() + "' and gianhap=" + row2["gianhap"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["id"].ToString();
                    row["madp"] = row2["ma"].ToString();
                    row["tendp"] = row2["ten"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["hamluong"] = row2["hamluong"].ToString();
                    row["losx"] = row2["losx"].ToString();
                    row["handung"] = row2["handung"].ToString();
                    row["hangsx"] = row2["hangsx"].ToString();
                    row["idnhom"] = row2["idnhom"].ToString();
                    row["idloai"] = row2["idloai"].ToString();
                    row["tennhom"] = row2["tennhom"].ToString();
                    row["tenloai"] = row2["tenloai"].ToString();
                    row["tenvitrikho"] = "";
                    row["gianhap"] = decimal.Parse(row2["gianhap"].ToString());
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = 0;
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["slxuat"] = decimal.Parse(row["slxuat"].ToString()) + decimal.Parse(row2["slxuat"].ToString());
                row["sltondau"] = (decimal.Parse(row["sltoncuoi"].ToString()) - decimal.Parse(row["slnhap"].ToString())) + decimal.Parse(row["slxuat"].ToString());
            }
            return set;
        }

        public DataSet f_load_tonkhochitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string[] strArray = formatdate.Split(new char[] { ' ' });
            DateTime time = new DateTime(int.Parse(denngay.Substring(6, 4)), int.Parse(denngay.Substring(3, 2)), int.Parse(denngay.Substring(0, 2)));
            if (strArray.Length > 1)
            {
                string[] strArray2 = strArray[1].Split(new char[] { ':' });
                try
                {
                    time.AddHours(double.Parse(strArray2[0]));
                }
                catch
                {
                }
                try
                {
                    time.AddMinutes(double.Parse(strArray2[1]));
                }
                catch
                {
                }
                try
                {
                    time.AddSeconds(double.Parse(strArray2[2]));
                }
                catch
                {
                }
            }
            time = time.AddDays(1.0);
            DataSet set = new DataSet();
            DataSet set2 = this.f_load_tonkhochitiet(tungay, formatdate, manhom, maloai);
            DataSet set3 = this.f_load_tonkhochitiet(time.ToString(formatdate), formatdate, manhom, maloai);
            set = set2.Copy();
            foreach (DataRow row2 in set3.Tables[0].Select("true", "iddp"))
            {
                DataRow row;
                DataRow[] rowArray = set.Tables[0].Select("iddp=" + row2["iddp"].ToString() + " and losx='" + row2["losx"].ToString() + "' and handung='" + row2["handung"].ToString() + "' and gianhap=" + row2["gianhap"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["iddp"].ToString();
                    row["madp"] = row2["madp"].ToString();
                    row["tendp"] = row2["tendp"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["hamluong"] = row2["hamluong"].ToString();
                    set.Tables[0].Rows.Add(row);
                }
                row["sltoncuoi"] = decimal.Parse(row2["sltondau"].ToString());
                row["slnhap"] = decimal.Parse(row["slnhap"].ToString()) - decimal.Parse(row2["slnhap"].ToString());
                row["slxuat"] = decimal.Parse(row["slxuat"].ToString()) - decimal.Parse(row2["slxuat"].ToString());
            }
            DataView defaultView = set.Tables[0].DefaultView;
            defaultView.Sort = "tendp";
            DataSet set4 = new DataSet();
            set4.Tables.Add(defaultView.ToTable().Copy());
            return set4;
        }

        public DataSet f_load_tonkhotonghop()
        {
            DataSet set = new DataSet();
            string sql = "select iddp,madp,tendp,tenhoatchat,dvt,sum(soluongton) as soluongton,0.0 as giaban  from(" + this.f_getsql_tonkhoct(0, "", "") + ") a  group by iddp,tendp,dvt,madp,tenhoatchat  order by tendp";
            set = this._xuli.f_Get_dulieu(sql);
            DataSet set2 = this.f_load_tonkhochitiet(HangSo.NhomKhachHang.enNhomKhachHang.Thuong, 0);
            foreach (DataRow row in set.Tables[0].Rows)
            {
                try
                {
                    row["giaban"] = Math.Round(decimal.Parse(set2.Tables[0].Select("iddp=" + row["iddp"].ToString(), "giaban desc")[0]["giaban"].ToString()), 2);
                }
                catch
                {
                }
            }
            return set;
        }

        public DataSet f_load_tonkhotonghop(string ngay, string formatdate)
        {
            DataRow row;
            DataRow[] rowArray;
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            DataSet set2 = this.f_load_tonkhotonghop();
            DataSet set3 = this.f_load_nhaptonghop(ngay, DateTime.Now.ToString(formatdate), formatdate, "", "");
            DataSet set4 = this.f_load_xuattonghop(ngay, DateTime.Now.ToString(formatdate), formatdate, "", "");
            string sql = "select 0.0 as iddp,'' as madp,'' as tendp,'' as tenhoatchat,'' as dvt,0.0  as sltondau,0.0 as slnhap,0.0 as slxuat,0.0 as sltoncuoi  from " + str + dtoDDMDuocPham.tableName + " where 1=0";
            set = this._xuli.f_Get_dulieu(sql);
            foreach (DataRow row2 in set2.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["iddp"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["iddp"].ToString();
                    row["madp"] = row2["madp"].ToString();
                    row["tendp"] = row2["tendp"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = decimal.Parse(row2["soluongton"].ToString());
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["sltondau"] = decimal.Parse(row["sltondau"].ToString()) + decimal.Parse(row["sltoncuoi"].ToString());
            }
            foreach (DataRow row2 in set3.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["id"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["id"].ToString();
                    row["madp"] = row2["ma"].ToString();
                    row["tendp"] = row2["ten"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = 0;
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["slnhap"] = decimal.Parse(row["slnhap"].ToString()) + decimal.Parse(row2["slnhap"].ToString());
                row["sltondau"] = decimal.Parse(row["sltoncuoi"].ToString()) - decimal.Parse(row["slnhap"].ToString());
            }
            foreach (DataRow row2 in set4.Tables[0].Rows)
            {
                rowArray = set.Tables[0].Select("iddp=" + row2["id"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["id"].ToString();
                    row["madp"] = row2["ma"].ToString();
                    row["tendp"] = row2["ten"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                    row["slnhap"] = 0;
                    row["slxuat"] = 0;
                    row["sltoncuoi"] = 0;
                    row["sltondau"] = 0;
                    set.Tables[0].Rows.Add(row);
                }
                row["slxuat"] = decimal.Parse(row["slxuat"].ToString()) + decimal.Parse(row2["slxuat"].ToString());
                row["sltondau"] = (decimal.Parse(row["sltoncuoi"].ToString()) - decimal.Parse(row["slnhap"].ToString())) + decimal.Parse(row["slxuat"].ToString());
            }
            return set;
        }

        public DataSet f_load_tonkhotonghop(string tungay, string denngay, string formatdate)
        {
            string[] strArray = formatdate.Split(new char[] { ' ' });
            DateTime time = new DateTime(int.Parse(denngay.Substring(6, 4)), int.Parse(denngay.Substring(3, 2)), int.Parse(denngay.Substring(0, 2)));
            if (strArray.Length > 1)
            {
                string[] strArray2 = strArray[1].Split(new char[] { ':' });
                try
                {
                    time.AddHours(double.Parse(strArray2[0]));
                }
                catch
                {
                }
                try
                {
                    time.AddMinutes(double.Parse(strArray2[1]));
                }
                catch
                {
                }
                try
                {
                    time.AddSeconds(double.Parse(strArray2[2]));
                }
                catch
                {
                }
            }
            time = time.AddDays(1.0);
            DataSet set = new DataSet();
            DataSet set2 = this.f_load_tonkhotonghop(tungay, formatdate);
            DataSet set3 = this.f_load_tonkhotonghop(time.ToString(formatdate), formatdate);
            set = set2.Copy();
            foreach (DataRow row2 in set3.Tables[0].Rows)
            {
                DataRow row;
                DataRow[] rowArray = set.Tables[0].Select("iddp=" + row2["iddp"].ToString());
                try
                {
                    row = rowArray[0];
                }
                catch
                {
                    row = set.Tables[0].NewRow();
                    row["iddp"] = row2["iddp"].ToString();
                    row["madp"] = row2["madp"].ToString();
                    row["tendp"] = row2["tendp"].ToString();
                    row["tenhoatchat"] = row2["tenhoatchat"].ToString();
                    row["dvt"] = row2["dvt"].ToString();
                }
                row["sltoncuoi"] = decimal.Parse(row2["sltondau"].ToString());
                row["slnhap"] = decimal.Parse(row["slnhap"].ToString()) - decimal.Parse(row2["slnhap"].ToString());
                row["slxuat"] = decimal.Parse(row["slxuat"].ToString()) - decimal.Parse(row2["slxuat"].ToString());
            }
            return set;
        }

        public DataSet f_load_xuatchitiet(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            string sql = "select  id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,sum(slxuat) as slxuat,idnhom,idloai,tennhom,tenloai from (" + this.f_getsql_xuatchitiet(tungay, denngay, formatdate, manhom, maloai) + ") a where 1=1   group by id, ma, ten, tenhoatchat, dvt, hamluong,losx,handung,hangsx,gianhap,idnhom,idloai,tennhom,tenloai order by ten";
            return this._xuli.f_Get_dulieu(sql);
        }

        public DataSet f_load_xuattonghop(string tungay, string denngay, string formatdate, string manhom, string maloai)
        {
            string str = this._xuli.pSchema + ".";
            DataSet set = new DataSet();
            string sql = "select  id, ma, ten, tenhoatchat, dvt, hamluong,sum(slxuat) as slxuat from (" + this.f_getsql_xuatchitiet(tungay, denngay, formatdate, manhom, maloai) + ") a where 1=1   group by id, ma, ten, tenhoatchat, dvt, hamluong order by ten";
            return this._xuli.f_Get_dulieu(sql);
        }

        public decimal f_update_nhapkho(dtoDNhapKho ToaThuoc, dtoDNhapKhoCT[] chitiet)
        {
            decimal pID;
            this._conn = new NpgsqlConnection(this._condata.StringConnect);
            this._conn.Open();
            NpgsqlTransaction transaction = this._conn.BeginTransaction();
            try
            {
                if (!ToaThuoc.Update(this._conn))
                {
                    transaction.Rollback();
                    return 0M;
                }
                for (int i = 0; i < chitiet.Length; i++)
                {
                    if (chitiet[i].pID != 0L)
                    {
                        if (!chitiet[i].Update(this._conn))
                        {
                            transaction.Rollback();
                            return 0M;
                        }
                    }
                    else
                    {
                        chitiet[i].pID = long.Parse(this._xuli.f_Get_CapID());
                        if (!chitiet[i].Insert(this._conn))
                        {
                            transaction.Rollback();
                            return 0M;
                        }
                    }
                }
                transaction.Commit();
                pID = ToaThuoc.pID;
            }
            catch
            {
                transaction.Rollback();
                pID = 0M;
            }
            finally
            {
                this._conn.Close();
                this._conn = null;
            }
            return pID;
        }
    }
}

